﻿IF OBJECT_ID ('CHO_USERS') IS NOT NULL
	DROP TABLE CHO_USERS
GO

CREATE TABLE CHO_USERS
(
    ID INT NOT NULL,
    NAME NVARCHAR(50) NOT NULL,
    FULL_NAME NVARCHAR(250) NOT NULL,
    PASSWORD NVARCHAR(50) NULL,
	ENABLED BIT NOT NULL DEFAULT(1),
    CONSTRAINT PK_CHO_USERS PRIMARY KEY (ID ASC),
    CONSTRAINT UQ_CHO_USERS UNIQUE (NAME)
)
GO

----------------------------------------------------------------

IF OBJECT_ID ('CHO_ADD_OR_UPDATE_USER') IS NOT NULL
	DROP PROC CHO_ADD_OR_UPDATE_USER
GO

CREATE PROCEDURE CHO_ADD_OR_UPDATE_USER
		@NAME NVARCHAR(50),
	    @FULL_NAME NVARCHAR(250),
		@PASSWORD NVARCHAR(50),
		@ENABLED BIT,
		@ID INT OUTPUT
		AS

	SET NOCOUNT ON

	SELECT @ID = NULL

	SELECT @ID = ID FROM CHO_USERS WHERE NAME = @NAME
	IF @ID IS NULL
	BEGIN
		INSERT INTO CHO_USERS (NAME, FULL_NAME, PASSWORD, ENABLED)
			VALUES (@NAME, @FULL_NAME, @PASSWORD, @ENABLED)

		SELECT @ID = @@IDENTITY
	END	
	ELSE
	BEGIN
		UPDATE CHO_USERS
		SET
			FULL_NAME = @FULL_NAME,
			PASSWORD = @PASSWORD,
			ENABLED = @ENABLED
		WHERE
			NAME = @NAME
	END
GO

----------------------------------------------------------------

IF OBJECT_ID ('CHO_DELETE_USER') IS NOT NULL
	DROP PROC CHO_DELETE_USER
GO

CREATE PROCEDURE CHO_DELETE_USER
		@NAME NVARCHAR(50)
		AS

	SET NOCOUNT ON

	DECLARE @ID INT

	SELECT @ID = NULL

	SELECT @ID = ID FROM CHO_USERS WHERE NAME = @NAME
	IF @ID IS NOT NULL
	BEGIN
		DELETE CHO_USER_ROLES
		WHERE
			USER_ID = @ID

		DELETE CHO_USERS
		WHERE
			NAME = @NAME
	END
GO

----------------------------------------------------------------

IF OBJECT_ID ('CHO_SELECT_USERS') IS NOT NULL
	DROP PROC CHO_SELECT_USERS
GO

CREATE PROCEDURE CHO_SELECT_USERS
		AS

	SET NOCOUNT ON

	SELECT ID, NAME, FULL_NAME, PASSWORD, ENABLED FROM CHO_USERS ORDER BY NAME
GO
